﻿using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;


using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Common;
using System.Security.Cryptography;

namespace Reports
{
    /// <summary>
    /// 数据访问抽象基础类
    /// Copyright (C) Maticsoft
    /// </summary>
    public abstract class DbHelperSQL
    {

        /// <summary>
        /// 加密链接字符串，执行SQL语句
        /// </summary>
        /// <param name="queryString">SQL语句</param>
        /// <param name="connectionString">加密链接字符串</param>
        /// <returns></returns>
        public static DataSet SelectRows(string queryString, string connectionString)
        {
            DataSet dataset = new DataSet();

            using (SqlConnection connection = new SqlConnection(Decrypt(connectionString, BaseConfig.SKey)))
            {
                try
                {
                    Log.WriteLog("开始查询数据 SelectRows Begin at " + DateTime.Now.ToString());
                    connection.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(queryString, connection);
                    adapter.Fill(dataset, "Store");
                    connection.Close();

                    Log.WriteLog("结束查询数据SelectRows End at " + DateTime.Now.ToString());

                    return dataset;
                }
                catch (System.Data.SqlClient.SqlException er)
                {
                    //tf = false;
                    Log.WriteLog("查询数据出错 SelectRows Error: " + er.ToString() + queryString);
                    return null;
                }
            }
        }

        /// <summary>
        /// 未加密链接字符串 ，执行SQL语句 
        /// </summary>
        /// <param name="queryString">SQL语句</param>
        /// <param name="connectionString">未加密链接字符串</param>
        /// <returns></returns>
        public static DataSet SelectRowsTest(string queryString, string connectionString)
        {
            DataSet dataset = new DataSet();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    Log.WriteLog("开始查询数据 SelectRows Begin at " + DateTime.Now.ToString());
                    connection.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(queryString, connection);
                    adapter.Fill(dataset, "Store");
                    connection.Close();

                    Log.WriteLog("结束查询数据SelectRows End at " + DateTime.Now.ToString());

                    return dataset;
                }
                catch (System.Data.SqlClient.SqlException er)
                {
                    //tf = false;
                    Log.WriteLog("查询数据出错 SelectRows Error: " + er.ToString() + queryString);
                    return null;
                }
            }
        }

        /// <summary>
        /// 解密链接字符串， 执行存储过程 
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <param name="connectionString">加密后的链接字符串</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, string connectionString )
        {
            using (SqlConnection connection = new SqlConnection(Decrypt(connectionString, BaseConfig.SKey)))
            {
                DataSet dataSet = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter sqlDA = new SqlDataAdapter();
                    sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                    sqlDA.Fill(dataSet, tableName);
                }
                catch (System.Exception ex)
                {
                    Log.WriteLog("查询数据出错 RunProcedure Error: " + ex.ToString());
                }
                finally
                {
                    connection.Close();
                }
                return dataSet;
            }
        }

        /// <summary>
        /// 未加密链接字符串 ，执行存储过程 
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <param name="connectionString">未加密链接字符串</param>
        /// <returns></returns>
        public static DataSet RunProcedureTest(string storedProcName, IDataParameter[] parameters, string tableName, string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter sqlDA = new SqlDataAdapter();
                    sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                    sqlDA.Fill(dataSet, tableName);
                }
                catch (System.Exception ex)
                {
                    Log.WriteLog("查询数据出错 RunPr/cedure Error: " + ex.ToString());
                }
                finally
                {
                    connection.Close();
                }
                return dataSet;
            }
        }

        /// <summary>
        /// 构建 SqlCommand 对象(用来辔回一个;�果集，而不是一个整慠值)
        /// </summary>
        /// <paral name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    if (parameter.SqlDbType == SqlDbType.DateTime)
                    {
                        if ((DateTime)parameter.Value == DateTime.MinValue)
                            parameter.Value = System.DBNull.Value;
                    }
                    if (parameter.Value == null) { parameter.Value = System.DBNull.Value; }
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }

        /// <summary>
        /// 未加密 返回数据列表 
        /// </summary>
        /// <param name="storeProcName">存储过程名</param>
        /// <param name="connectionString">未加密</param>
        /// <returns></returns>
        private static DataSet GetTableTest(string storeProcName, string connectionString)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    con.Open();
                    SqlDataAdapter sda = new SqlDataAdapter();
                    sda.SelectCommand = BuildQueryCommand(con, storeProcName, null);
                    sda.Fill(ds);
                }
                catch (Exception ex)
                {
                    Log.WriteLog(ex);
                }
                finally
                {
                    con.Close();
                }
                return ds;
            }
        }
        /// <summary>
        /// 加密 返回数据列表  
        /// </summary>
        /// <param name="storeProcName">存储过程名</param> 
        /// <param name="connectionString">加密</param>
        /// <returns></returns>
        private static DataSet GetTable(string storeProcName, string connectionString)
        {
            using (SqlConnection con = new SqlConnection(Decrypt(connectionString, BaseConfig.SKey)))
            {
                DataSet ds = new DataSet();
                try
                {
                    con.Open();
                    SqlDataAdapter sda = new SqlDataAdapter();
                    sda.SelectCommand = BuildQueryCommand(con, storeProcName, null);
                    sda.Fill(ds);
                }
                catch (Exception ex)
                {
                    Log.WriteLog(ex);
                }
                finally
                {
                    con.Close();
                }
                return ds;
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="_ReportType">类型</param>
        /// <param name="_StartTime">开始时间</param>
        /// <param name="_EndTime">结束时间</param>
        /// <returns></returns>
        public static DataSet GetList(int _ReportType, DateTime _StartTime, DateTime _EndTime)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@ReportType", SqlDbType.Int,4),
                    new SqlParameter("@StartTime", SqlDbType.Date),
                    new SqlParameter("@EndTime", SqlDbType.Date)};
            parameters[0].Value = _ReportType;
            parameters[1].Value = _StartTime;
            parameters[2].Value = _EndTime;

            DataSet ds = new DataSet();
            ds = RunProcedureTest("Proc_Report_EmailAutoSend", parameters, "ds",BaseConfig.Web);
            //if (ds.Tables.Count == 0)
            //{
            //    //tf = false;
            //}
            //else
            //{
            //    if (ds.Tables[0].Rows.Count == 0)
            //        //tf = false;
            //    else
            //        //tf = true;
            //}
            return ds;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="_ReportType">类型</param>
        /// <param name="_StartTime">开始时间</param>
        /// <param name="_EndTime">结束时间</param>
        /// <param name="CategoryFamily">商品分类ID</param>
        /// <returns></returns>
        public static DataSet GetListByType(int _ReportType, DateTime _StartTime, DateTime _EndTime, string CategoryFamily)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@ReportType", SqlDbType.Int,4),
                    new SqlParameter("@StartTime", SqlDbType.Date),
                    new SqlParameter("@EndTime", SqlDbType.Date),
                    new SqlParameter("@CategoryFamily",SqlDbType.VarChar)};
            parameters[0].Value = _ReportType;
            parameters[1].Value = _StartTime;
            parameters[2].Value = _EndTime;
            parameters[3].Value = CategoryFamily;
            DataSet ds = new DataSet();
            ds = RunProcedureTest("Proc_Report_EmailAutoSend", parameters, "ds",BaseConfig.Web);//RunProcedureTest("Proc_Report_EmailAutoSend", parameters, "ds");
            //if (ds.Tables.Count == 0)
            //{
            //    //tf = false;
            //}
            //else
            //{
            //    if (ds.Tables[0].Rows.Count == 0)
            //        //tf = false;
            //    else
            //        //tf = true;
            //}
            return ds;
        }



        #region ========解密========


        /// <summary>
        /// 解密
        /// </summary>
        /// <param name="Text"></param>
        /// <returns></returns>
        public static string Decrypt(string Text, string Key)
        {
            return GDecrypt(Text, Key);
        }
        /// <summary> 
        /// 解密数据 
        /// </summary> 
        /// <param name="Text"></param> 
        /// <param name="sKey"></param> 
        /// <returns></returns> 
        public static string GDecrypt(string Text, string sKey)
        {
            DESCryptoServiceProvider des = new DESCryptoServiceProvider();
            int len;
            len = Text.Length / 2;
            byte[] inputByteArray = new byte[len];
            int x, i;
            for (x = 0; x < len; x++)
            {
                i = Convert.ToInt32(Text.Substring(x * 2, 2), 16);
                inputByteArray[x] = (byte)i;
            }

            des.Key = ASCIIEncoding.ASCII.GetBytes(
                System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
            des.IV = ASCIIEncoding.ASCII.GetBytes(
                System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));

            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write);
            cs.Write(inputByteArray, 0, inputByteArray.Length);
            cs.FlushFinalBlock();
            return Encoding.Default.GetString(ms.ToArray());
        }

        #endregion



    }

}
